<#assign StringUtils = beansWrapperFn.getStaticModels()["org.apache.commons.lang3.StringUtils"]>
WITH w_site AS
(SELECT t.*, row_number() over(order by t.code) rn ,
project_DICTIONARY.code as projectCode ,
project_DICTIONARY.name as projectName
FROM C1_SITE T
left join C1_DICTIONARY project_DICTIONARY on (project_DICTIONARY.id = t.project_ID)
where (Sysdate BETWEEN t.begin_date AND t.end_date)
<#if StringUtils.isNotBlank(projectCode)>
and (instr(','||project_DICTIONARY.code||',', ','||:projectCode||',') > 0 or instr(','||project_DICTIONARY.name||',', ','||:projectCode||',') > 0)
</#if>
<#if StringUtils.isNotBlank(code)>
and (instr(','||T.code||',', ','||:code||',') > 0 or instr(','||T.name||',', ','||:code||',') > 0)
</#if>
)
SELECT T.BEGIN_DATE AS beginDate ,
T.CODE             AS code ,
T.END_DATE         AS endDate ,
T.ID               AS id ,
T.NAME             AS name ,
T.PROJECT_ID       AS projectId ,
T.STYLE_ID         AS styleId ,
style_DICTIONARY.code as styleCode ,
style_DICTIONARY.name as styleName,
T.projectCode ,
T.projectName
FROM w_site t
left join C1_DICTIONARY style_DICTIONARY on (style_DICTIONARY.id = t.STYLE_ID)
WHERE t.rn BETWEEN :begin_row_num AND :end_row_num
order by t.rn